package universal.schema.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.opensymphony.xwork2.ActionContext;

import universal.schema.util.DB;
import universal.schema.util.RuntimeSettings;


public class Graph {

	public static ArrayList<Node> hash_bucket = new ArrayList<Node>();
	public static ArrayList<Node> linker_nodes = new ArrayList<Node>();
	public static ArrayList<Node> linker_nodes_association = new ArrayList<Node>();
	ArrayList<Node> join_keys=new ArrayList<Node>();
	ArrayList<Node> join_keys_assciation=new ArrayList<Node>();

	public ArrayList<Node> getJoin_keys() {
		return join_keys;
	}

	public void setJoin_keys(ArrayList<Node> join_keys) {
		this.join_keys = join_keys;
	}

	public static ArrayList<Node> getLinker_nodes() {
		return linker_nodes;
	}




	public static void setLinker_nodes(ArrayList<Node> linker_nodes) {
		Graph.linker_nodes = linker_nodes;
	}

	public ArrayList<Node> getHash_bucket() {
		return hash_bucket;
	}

	public void setHash_bucket(ArrayList<Node> hash_bucket) {
		Graph.hash_bucket = hash_bucket;
	}

	public ArrayList<Node> getJoin_keys_assciation() {
		return join_keys_assciation;
	}

	public void setJoin_keys_assciation(ArrayList<Node> join_keys_assciation) {
		this.join_keys_assciation = join_keys_assciation;
	}



	public static ArrayList<Node> getLinker_nodes_association() {
		return linker_nodes_association;
	}

	public static void setLinker_nodes_association(
			ArrayList<Node> linker_nodes_association) {
		Graph.linker_nodes_association = linker_nodes_association;
	}

	//creates the hashbucket
	public static ArrayList<Node> initHashBucket() throws Exception {
		hash_bucket.clear();
		String sql = "select distinct table_name from " +
				"information_schema.key_column_usage where table_schema='"+RuntimeSettings.databaseName +"';";

		ResultSet resultSet = null;
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(sql, connection);
		while (resultSet.next()) {

			//initializing table object
			Table tmp = new Table();
			tmp.setName(resultSet.getString("table_name"));
			tmp.setColumns(hashBucketColumns(tmp.getName()));

			//initializing node object
			Node nodeTemp=new Node();
			nodeTemp.setTblObj(tmp);
			nodeTemp.setForeignKey(null);
			nodeTemp.setPrimaryKey(null);
			hash_bucket.add(nodeTemp);
		}

		return hash_bucket;
	}

	static ArrayList<Column> hashBucketColumns(String tableName) throws SQLException {
		ArrayList<Column> cols = new ArrayList<Column>();

		String sql = "select concat(concat(TABLE_NAME,'.'),COLUMN_NAME) as Col, DATA_TYPE from information_schema.COLUMNS " +
				"where table_schema='"+RuntimeSettings.databaseName +"' " +
				"and TABLE_NAME='"+ tableName + "';";

		String sql1 = "select distinct concat(concat(a.TABLE_NAME,'.'),a.COLUMN_NAME) as colName, " +
				"b.constraint_type as constType " +
				"from information_schema.KEY_COLUMN_USAGE a,information_schema.TABLE_CONSTRAINTS b " +
				"where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and a.CONSTRAINT_SCHEMA=b.CONSTRAINT_SCHEMA " +
				"and a.TABLE_NAME=b.TABLE_NAME and b.CONSTRAINT_SCHEMA='"+RuntimeSettings.databaseName +"' and b.TABLE_NAME='"
				+ tableName + "';";

		ResultSet resultSet = null;
		ResultSet resultSet1 = null;

		Connection connection = DB.getConnection();

		resultSet = DB.readFromDB(sql, connection);

		while (resultSet.next()) {
			Column colTemp = new Column();
			colTemp.setColName(resultSet.getString(1));
			colTemp.setColType(resultSet.getString(2));
			colTemp.setDataType("NK");

			resultSet1 = DB.readFromDB(sql1, connection);
			while(resultSet1.next()){

				/* if the column names match from the two queries */
				if(resultSet.getString(1).equals(resultSet1.getString(1))){

					/* if PK is detected and previously has not been assigned */
					if (resultSet1.getString(2).equals("PRIMARY KEY") && colTemp.getDataType().equals("NK"))
						colTemp.setDataType("PK"); // primary key
					/* if PK has been detected and previously assigned*/
					else if (resultSet1.getString(2).equals("PRIMARY KEY") && !(colTemp.getDataType().equals("NK")) )
						colTemp.setDataType("PK"); // primary key

					/* if FK is detected and previously has not been assigned */
					else if (resultSet1.getString(2).equals("FOREIGN KEY") && colTemp.getDataType().equals("NK"))
						colTemp.setDataType("FK"); // foreign key
					/* if FK has been detected and previously assigned*/
					else if (resultSet1.getString(2).equals("FOREIGN KEY") && !(colTemp.getDataType().equals("NK")) )
						colTemp.setDataType("FK"); // foreign key
				}
			}
		
			// attributes
			cols.add(colTemp);
		}

		connection.close();
		return cols;

	}

	public static  Node searchNode(String tableName){
		for(int i=0;i<hash_bucket.size();i++){
			if(hash_bucket.get(i).getTblObj().getName().equals(tableName)){
				//System.out.println("In search function="+hash_bucket.get(i).getPrimaryKey());
				return hash_bucket.get(i);
			}

		}
		return null;
	}


	public static void createLinks() throws SQLException{

		String sql = "select table_name,column_name,referenced_table_name,referenced_column_name " +
				"from information_schema.key_column_usage where table_schema='"+RuntimeSettings.databaseName +"' and " +
				"referenced_table_name is not null and referenced_column_name is not null;";

		ResultSet resultSet = null;
		Connection connection = DB.getConnection();
		resultSet = DB.readFromDB(sql, connection);
		while (resultSet.next()) {
			Node bucketNode;
			bucketNode=searchNode(resultSet.getString("table_name"));

			if(bucketNode!=null){

				//TABLE OBJ
				Table tmpObj = new Table();
				tmpObj.setName(resultSet.getString("referenced_table_name"));
				tmpObj.setColumns(null);

				//NODE OBJ
				Node tempNode = new Node();
				tempNode.setTblObj(tmpObj);
				tempNode.setPrimaryKey(resultSet.getString("referenced_column_name"));
				tempNode.setForeignKey(resultSet.getString("column_name"));
				tempNode.setLinkedNodes(null);

				//linking node
				bucketNode.addLinkedNode(tempNode);
			}

		}

		/*System.out.println("----------------------ADJACENCY LIST REALIZATION----------------------\n");
		System.out.println("HASH BUCKET <Column List> 		REFERENCES		(REFERENCED TABLE, PK, FK) LISTS\n"); 
		for(int i=0;i<hash_bucket.size();i++) {
			System.out.println(" ");
			System.out.println("-------------------------");
			System.out.print(hash_bucket.get(i).getTblObj().getName()+" <");
			for(int k=0;k<hash_bucket.get(i).getTblObj().getColumns().size();k++)
				System.out.print(hash_bucket.get(i).getTblObj().getColumns().get(k).getColName() + ", ");
			System.out.println(">	REFERENCES 	");

			System.out.print(" (");
			for(int j=0;j<hash_bucket.get(i).getLinkedNodes().size();j++){
				System.out.print(hash_bucket.get(i).getLinkedNodes().get(j).getTblObj().getName()+" [");
				System.out.print("PK : "+ hash_bucket.get(i).getLinkedNodes().get(j).getPrimaryKey()+",");
				System.out.print(" FK : "+ hash_bucket.get(i).getLinkedNodes().get(j).getForeignKey()+ "]");
				System.out.print(" -- ");
			}
			System.out.println(")");
		}
		
*/		 	
		
	}

	//DFS to check connectivity
	public static boolean isConnected(String main_src,String src,String dst)
	{
		int i=0;
		boolean flag=false;

		Node start = searchNode(src);
		linker_nodes.add(start); // path
	
		if(start!=null)
		{
			//if path is not found..clear the linker_nodes and start with next node in the linked nodes
			if(start.getLinkedNodes().isEmpty()) // check adj nodes
			{
				linker_nodes.clear();
				linker_nodes.add(searchNode(main_src));
			}
			
			//DFS
			for(i=0;i<start.linkedNodes.size();i++)
			{
					//DIRECT LINK
				if(start.linkedNodes.get(i).getTblObj().getName().equalsIgnoreCase(dst))
				{
					flag=true;
					linker_nodes.add(start.linkedNodes.get(i));
					return flag;
				}
				
				//NON DIRECT LINK
				else
				{
					//RECURSION 
					flag=isConnected(main_src,start.linkedNodes.get(i).getTblObj().getName(),dst);	
					if(flag==true)
					{
						return flag;
					}
				}
			 }
		}
		
		return flag;

	}
	
	//checking connectivity in case of association class
	public static boolean isAssociation(String src,String dst){

		//linker_nodes_association.clear();

		ArrayList<Integer> checkPoints = new ArrayList<Integer>();
		int i,j;
		boolean flag = false;

		if(src.equals(dst)){
			System.out.println("Source == destination");
			return flag;
		}
		else{
			//checking for src index
			for(i=0;i<hash_bucket.size();i++){

				for(j=0;j<hash_bucket.get(i).getLinkedNodes().size();j++){

					if(hash_bucket.get(i).getLinkedNodes().get(j).getTblObj().getName().equals(src)){
						checkPoints.add(i); // if src table there in linker nodes of hash bucket
						break;
					}//end of if
				}//end of inner for
			}//end of outer for

			//Adding actual Source Node
			if(checkPoints.size()!=0)
			{
				for(i=0;i<hash_bucket.get(checkPoints.get(0)).getLinkedNodes().size();i++)
				{
					if(hash_bucket.get(checkPoints.get(0)).getLinkedNodes().get(i).getTblObj().getName().equals(src))
					{
						linker_nodes_association.add(hash_bucket.get(checkPoints.get(0)).getLinkedNodes().get(i));//adding source node
						break;
					}
				}
			}

			//navigating thru checkPoints to check assoication between src and dest

			for(i=0;i<checkPoints.size();i++){

				//looping thru linked nodes of each hashbucket in checkpoints
				for(j=0;j<hash_bucket.get(checkPoints.get(i)).getLinkedNodes().size();j++){

					if(hash_bucket.get(checkPoints.get(i)).getLinkedNodes().get(j).getTblObj().getName().equals(dst)){
						flag=true;
						linker_nodes_association.add(hash_bucket.get(checkPoints.get(i))); // adding intermediate node (association class)
						linker_nodes_association.add(hash_bucket.get(checkPoints.get(i)).getLinkedNodes().get(j)); //adding destination node
						break;
					}// end of if
				}// end of inner for

				//if path is found, no need to proceed furthur
				if(flag==true)
					break;
			}//end of outer for

		}
		return flag;
	}


	public Node getLinkedNode(Node a, Node b)
	{
		Node tmp=searchNode(a.getTblObj().getName());
		if(tmp!= null){
			for(int l=0;l<tmp.getLinkedNodes().size();l++)
			{
				if(tmp.getLinkedNodes().get(l).getTblObj().getName().equalsIgnoreCase(b.getTblObj().getName()) )
				{
					return tmp.getLinkedNodes().get(l);
				}

			}//end for
		}//end if
		return null;
	}//end func


	//Getting all info about linked nodes.Used when working with isConnected()

	public void trial(ArrayList<Node> linkernodes)
	{
		//join keys - except first node all other nodes will have pks and fks.only name is needed for the first/src node
	
		join_keys.add(linkernodes.get(0)); //only table name

		for(int i=1;i<=linkernodes.size()-1;i++)
		{
			join_keys.add(getLinkedNode(linkernodes.get(i-1),linkernodes.get(i)) );
		}

	}
}
